import groovy.transform.ToString
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.xssf.usermodel.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook

/**
 * @author Qilin Tan
 *
 */

@Grapes([
        @Grab(group = 'org.apache.poi', module = 'poi', version = '3.10.1'),
        @Grab(group = 'org.apache.poi', module = 'poi-ooxml', version = '3.10.1')]
)

@ToString(includeFields = true)
class FoodInfo {
    def name
    def region
    def ediblePart
    def power
    def cholesterol
}

class ConvertExcel {
    def readxml(String path) {
        def input = new FileInputStream(path);
        XSSFWorkbook workbook = new XSSFWorkbook(input)
        int sheetNum = workbook.numberOfSheets;
        def data = [:]
        (0..<sheetNum).each { sheetIndex ->
            if (!workbook.isSheetHidden(sheetIndex)) {
                data << readSheet(workbook, sheetIndex)
            }
        }
        return data
    }

    def readSheet(XSSFWorkbook workbook, int sheetIndex) {
        def sheet = workbook.getSheetAt(sheetIndex)
        def rowNum = sheet.physicalNumberOfRows
        def rowValues = []
        def data = [:]
        def cellNum = sheet.getRow(0).physicalNumberOfCells
        (0..<rowNum).each { index ->
            def row = sheet.getRow(index)
            def cellValues = readRow(row, cellNum)
            rowValues << cellValues;
        }
        data.put(sheet.sheetName, rowValues as Object[][])
        data
    }

    def readRow(XSSFRow row, int cellNum) {

        if (row.physicalNumberOfCells > cellNum)
            cellNum = row.physicalNumberOfCells
        def cells = []
        (0..<cellNum).each { index ->
            def cell = row.getCell(index)
            def value = getCellValue(cell)
            cells << value
        }
        cells as Object[]
    }

    def writeToExcel(String path, Map<String, Object> data) {
        XSSFWorkbook workbook = new XSSFWorkbook()
        data.each { k, v ->
            println k
            if (k in ['食材原产地',
                      '食材微量元素含量对照表',
                      '食材维生素含量表',
                      '食材营养元素含量表']) {
                createCleanFoodSheet(workbook, k, v)
            } else {
                createKeepSheet(workbook, k, v)
            }
//            if (k == "食材信息") {
//                createKeepSheet(workbook, k, v)
//            } else {
//                def header
//                if (k == '食材微量元素含量对照表')
//                    header = ['食材Key', '微量元素Key', '含量(mg)']
//                else if (k == '食材维生素含量表')
//                    header = ['食材Key', '维生素Key', '含量(mg)']
//                else if (k == '食材营养元素含量表')
//                    header = ['食材Key', '营养元素Key', '含量(mg)']
//                createCombineSheet(workbook, k, v, header)
//            }
        }
        workbook.write(new FileOutputStream(path))
    }

    def createSheet(XSSFWorkbook workbook, String name, Object[][] rowValues) {
        def sheet = workbook.createSheet(name)
        int iLen = rowValues.length
        int jLen = rowValues[0].length
        for (int j = 0; j < jLen; j++) {
            def row = sheet.createRow(j)
            for (int i = 0; i < iLen; i++) {
                def cell = row.createCell(i)
                cell.setCellValue(rowValues[i][j])
            }
        }
    }

    def createKeepSheet(XSSFWorkbook workbook, String name, Object[][] rowValues) {
        def sheet = workbook.createSheet(name)
        int iLen = rowValues.length
        int jLen = rowValues[0].length
        for (int i = 0; i < iLen; i++) {
            def row = sheet.createRow(i)
            for (int j = 0; j < jLen; j++) {
                def cell = row.createCell(j)
                try {
                    cell.setCellValue(rowValues[i][j])
                } catch (ArrayIndexOutOfBoundsException e) {
                    // e.printStackTrace()
                    cell.setCellValue(null)
                }
            }
        }
    }

    def createCleanFoodSheet(XSSFWorkbook workbook, String name, Object[][] rowValues) {
        def sheet = workbook.createSheet(name)
        int iLen = rowValues.length
        int jLen = rowValues[0].length
        def foodKeyIndexes = rowValues[0].findIndexValues { "食材Key" == it }
        println foodKeyIndexes
        for (int i = 0; i < iLen; i++) {
            def row = sheet.createRow(i)
            for (int j = 0; j < jLen; j++) {
                def cell = row.createCell(j)
                def value = null
                try {
                    value = rowValues[i][j]
                } catch (ArrayIndexOutOfBoundsException e) {
                    // e.printStackTrace()
                }
                if (value instanceof String && foodKeyIndexes.find{it == j}) {
                    def pos = value.indexOf('（')
                    if (pos != -1) {
                        pos--
                        value = value[0..pos]
                    }
                }
                cell.setCellValue(value)
            }
        }
    }
    def createCombineSheet(XSSFWorkbook workbook, String name, Object[][] rowValues, List<String> header) {
        def sheet = workbook.createSheet(name)
        int iLen = rowValues.length
        int jLen = rowValues[0].length
        def rowValue = rowValues[0]
        def row = sheet.createRow(0);
        for (int j = 0; j < header.size(); j++) {
            def cell = row.createCell(j)
            cell.setCellValue(header.get(j))
        }
        int rowNum = 1
        for (int i = 1; i < iLen; i++) {
            for (int j = 1; j < jLen; j++) {
                row = sheet.createRow(rowNum)
                for (int k = 0; k < header.size(); k++) {
                    def cell = row.createCell(k)
                    if (k == 0)
                        cell.setCellValue(rowValues[i][0])
                    else if (k == 1)
                        cell.setCellValue(rowValue[j])
                    else if (k == 2)
                        cell.setCellValue(rowValues[i][j])
                }
                rowNum++
            }
        }
    }

    def getCellValue(cell) {
        if (cell == null)
            return ''
        def type = cell.cellType
        def value
        switch (type) {
            case Cell.CELL_TYPE_BLANK:
                value = ''
                break
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.booleanCellValue
                break
            case Cell.CELL_TYPE_NUMERIC:
                value = cell.numericCellValue
                break
            case Cell.CELL_TYPE_STRING:
            case Cell.CELL_TYPE_FORMULA:
                value = cell.stringCellValue
                break
            default:
                value = cell.stringCellValue
        }
        return value
    }
}

def test = new ConvertExcel()
def data = test.readxml("/home/qilin/Downloads/食材相关数据整理-1217.xlsx")
test.writeToExcel("/home/qilin/test.xlsx", data)